package database.util;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User: kostika
 * Date: 12/28/12
 * Time: 5:23 PM
 */

public class Queries {

    public static final String PERSON_INSERT_QUERY = getPersonInsertString();
    public static final String ACTIVITY_TYPES_SELECT_QUERY = getActivityTypesQuery();
    public static final String ACTIVITIES_SELECT_QUERY = getActivitiesQuery();
    public static final String INFORMATION_SOURCES_SELECT_QUERY = getInformationSourcesSelectQuery();
    public static final String ACTION_INFORMATION_INSERT_QUERY = getActionInformationInsertQuery();
    public static final String SELECT_NEXTVAL_SEQUENCE_QUERY = getNextValueSequenceQuery();
    public static final String ACTION_INFORMATION_SELECT_QUERY = getActionInformationSelectQuery();


    private static String getPersonInsertString(){
        StringBuilder builder = new StringBuilder();
        builder.append(" INSERT INTO public.person ( ");
        builder.append("id, emri , mbiemri , atesia , amesia , mbiemri_martesor, mbiemri_perdorur, ");
        builder.append("ditelindja , seksi , status_civil , vendlindja , vendbanimi , gjimnazi , shtetesia , ");
        builder.append("adresa , celular , telefon , telefon_reference , email , email_unizkm , gjuha_amtare , ");
        builder.append("besimi_fetar , pasaporta , karta_id , personal_no , status , portal_id )");
        builder.append(" VALUES ( ");
        builder.append(" ?, ?, ?, ?, ?, ?, ?, ");
        builder.append(" ?, ?, ?, ?, ?, ?, ?, ");
        builder.append(" ?, ?, ?, ?, ?, ?, ?, ");
        builder.append(" ?, ?, ?, ?, ?, ?  ) ");

        return builder.toString();
    }

    private static String getActivityTypesQuery(){
        StringBuilder builder = new StringBuilder();
        builder.append(" SELECT id, name, description FROM didactic.activity_type ORDER BY name");

        return builder.toString();
    }

    private static String getActivitiesQuery(){
        StringBuilder builder = new StringBuilder();
        builder.append(" SELECT id, type_id, name, description, validation_date, expire_date, enabled ");
        builder.append(" FROM didactic.activity ");
        builder.append(" WHERE validation_date <= CURRENT_TIMESTAMP");
        builder.append(" AND expire_date >= CURRENT_TIMESTAMP");
        builder.append(" ORDER BY name ");

        return builder.toString();
    }


    private static String getInformationSourcesSelectQuery(){
        StringBuilder builder = new StringBuilder();
        builder.append(" SELECT id, name, description, parent FROM didactic.information_sources ORDER BY parent");

        return builder.toString();
    }

    private static String getActionInformationInsertQuery(){
        StringBuilder builder = new StringBuilder();
        builder.append(" INSERT INTO sporteli.action_information ( ");
        builder.append(" id, person_id, date_action, data_interesimit, fusha_interesimit, nenfusha_interesimit, ");
        builder.append(" burimi_informacionit, nenburimi_informacionit, dokumentat, shenime )");
        builder.append(" VALUES ( ?, ?, ?, ?, ?, ?,  ");
        builder.append(" ?, ?, ?, ? )  ");

        return builder.toString();
    }

    private static String getActionInformationSelectQuery(){
        StringBuilder builder = new StringBuilder();
        builder.append(" SELECT  id, person_id, date_action, data_interesimit, fusha_interesimit, nenfusha_interesimit, ");
        builder.append(" burimi_informacionit, nenburimi_informacionit, dokumentat, shenime ");
        builder.append(" FROM sporteli.action_information  ");
        builder.append(" WHERE  data_interesimit >= ? AND data_interesimit <= ? ");
        builder.append(" ORDER BY data_interesimit ");

        return builder.toString();
    }

    private static String getNextValueSequenceQuery(){
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT nextval(?) ");

        return builder.toString();
    }
}
